﻿--获得本单位岗位异动人员数据
CREATE PROCEDURE [dbo].[proc_Employee_PostMove_GetList]
(
		@CompanyId NVARCHAR(400),
		@DepId nvarchar(50),
		@Stext nvarchar(50),
		@StartIndex nvarchar(50),
		@EndIndex nvarchar(50)

)
AS
DECLARE @swhere NVARCHAR(4000)
SET @swhere=''
IF(@CompanyId<>'0')
SET @swhere=@swhere+' ep.BCompanyId in('+@CompanyId+') and'
IF(@DepId<>'0')
SET @swhere=@swhere+' ep.BDepartmentId in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T) and'
IF(@Stext<>'')
SET @swhere=@swhere+' ep.Ename like ''%'+@Stext+'%'' and'
SET @swhere=@swhere+' ep.BCompanyId is not null '
DECLARE @sql NVARCHAR(4000)
SET @sql=
		+ 'declare @bmbh_T  varchar(50)'
		+ ' Select @bmbh_T=bmbh From department Where id='+@DepId+''
		+ ' ;WITH list As(Select ROW_NUMBER() OVER (ORDER BY Id DESC)AS Row,'
		+ ' Id,ep.EId,EName,'
		+ ' (Select e.Enumber From Employee e where e.Id=ep.Eid) as Enumber,'
		+ ' (Select e.ETempNumber From Employee e where e.Id=ep.Eid) as ETempNumber,'
		+ ' (Select e.Sex From Employee e where e.Id=ep.Eid) as Sex,'
		+ ' (select c.CompanyName from Company c where c.id=ep.BCompanyId )as CompanyName,'
		+ ' (select d.DName from Department d where d.id=ep.BDepartmentId) as DName,'
		+ ' (select p.PostName from Post p where p.id=ep.bpostid) as PostName,'
		+ ' (select p.PostName from Post p where p.id=ep.apostid) as APostName,'
		+ ' (select g.Title from Grade g where g.id=ep.BGradeId) as GradeName,'
		+ ' (select g.Title from Grade g where g.id=ep.AGradeId) as AGradeName,'
		+ ' ep.FirstAudit,ep.SecondAudit,ep.FirstAuditPerson,ep.SecondAuditPerson,'
		+ ' (Select e.EName From Employee e Where e.UserName=ep.Proposer) as Proposer,'
		+ ' ApplionTime'
		+ ' from Employee_PostMove  ep'
		+ ' Where'
		+ @swhere
		+ ' )'
		+ ' Select  *,(SELECT COUNT(0) FROM list) AS coun From list '
		+ ' Where Row Between '+ @StartIndex+' and '+ @EndIndex + ' Order By Row'
		
		EXEC(@sql)





----获得本单位岗位异动人员数据
--ALTER proc [dbo].[proc_Employee_PostMove_GetList]
--(
--		@CompanyId int,
--		@DepId int,
--		@Stext nvarchar(50),
--		@StartIndex int,
--		@EndIndex int

--)
--as
--begin
--			declare @bmbh_T  varchar(50)

--	Select @bmbh_T=bmbh From department Where id=@DepId
	
--	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY Id DESC)AS Row,
--		Id,
--		ep.EId,
--		 EName,
--		(Select e.Enumber From Employee e where e.Id=ep.Eid) as Enumber,
--		(Select e.ETempNumber From Employee e where e.Id=ep.Eid) as ETempNumber,
--		(Select e.Sex From Employee e where e.Id=ep.Eid) as Sex,
--		(select c.CompanyName from Company c where c.id=ep.BCompanyId )as CompanyName,
--		(select d.DName from Department d where d.id=ep.BDepartmentId) as DName,
----		(select p.PostName from Post p where p.id=((Select e.PostId From Employee e where e.Id=ep.Eid))) as PostName,
--		(select p.PostName from Post p where p.id=ep.bpostid) as PostName,
--		(select p.PostName from Post p where p.id=ep.apostid) as APostName,
--		(select g.Title from Grade g where g.id=ep.BGradeId) as GradeName,
----		(Select count(id) from Employee_PostMove ee	Where (@CompanyId=0 Or ee.BCompanyId=@CompanyId) and (@DepId=0 Or  ee.BDepartmentId in(select c.nid from @indextable c)) and (@Stext='' Or ee.Ename like '%'+@Stext+'%')) AS coun,
--		ep.FirstAudit,
--		ep.SecondAudit,
--		ep.FirstAuditPerson,
--		ep.SecondAuditPerson,
--		(Select e.EName From Employee e Where e.UserName=ep.Proposer) as Proposer,
--		ApplionTime
--		from Employee_PostMove  ep
		
--		Where 
--		(@CompanyId=0 Or ep.BCompanyId=@CompanyId)
--		and (@DepId=0 Or  ep.BDepartmentId in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
--		and (@Stext='' Or ep.Ename like '%'+@Stext+'%')
--	)
	
--	Select  *,(SELECT COUNT(0) FROM list) AS coun From list Where Row Between @StartIndex and @EndIndex
--end
	RETURN
